<?php namespace System;


class Db {
    private $mysqli;
    private $table;
    private $where   = [];
    private $select  = '*';
    private $orWhere = [];
    private $join    = [];
    private $group   = [];
    private $having;
    private $limit;
    private $order;
    private $lastQuery;
    private $result;
    private $resultArray;
    private $field   = [];
    public  $error;
    private $insertId;

    /**
     * Db constructor.
     * @param array||string $host
     * @param string $username
     * @param string $password
     * @param string $db
     * @param string $port
     * @param string $charset
     * @param string $socket
     */
    public function __construct($host = null, $username = null, $password = null, $db = null, $port = null, $charset = 'utf8', $socket = null) {
        $config = is_array($host) ? $host : [
            'host' => $host,
            'user' => $username,
            'pass' => $password,
            'db' => $db,
            'port' => $port,
            'charset' => $charset,
            'socket' => $socket
        ];
        $this->connect($config);
        return $this->mysqli;
    }

    /**
     * connect to database
     * @param array $config
     */
    public function connect($config) {
        $this->mysqli = new \mysqli($config['host'], $config['user'], $config['pass'], $config['db'], $config['port'], $config['socket']);
        if ($this->mysqli->connect_error) {
            $this->error = $this->mysqli->connect_error;
            exit();
        }
        $this->mysqli->set_charset($config['charset']);
        $this->mysqli->select_db($config['db']);
    }

    /**
     *
     * @param $key
     * @param string $val
     * @param null $operator
     * @param bool $or
     * @param string $likeLR
     * @return $this
     */
    public function where($key, $val = '', $operator = null, $or = FALSE, $likeLR = 'both') {
        $where = [];
        if (is_array($key)) {
            foreach ($key as $k => $v) {
                if (!is_numeric($k)) {
                    $where[] = (preg_match('/[>=<]/', $k)) ? ($k . " \"{$this->escape($v)}\"") : ($k . " = \"{$this->escape($v)}\"");
                } else {
                    $where[] = $v;
                }
            }
        } else if ($val && $operator) {
            switch (strtolower($operator)) {
                case 'in':
                case 'not in':
                    $not = strtolower($operator) != 'in' ? ' NOT' : '';
                    $where[] = ($key . $not . ' IN ("' . implode('","', $this->escape($val)) . '")');
                    break;
                case 'like':
                case 'not like':
                    $not = strtolower($operator) != 'like' ? ' NOT' : '';
                    if (strtolower($likeLR) == 'l') {
                        $where[] = ($key . $not . ' LIKE "%' . $this->escape($val) . '"');
                    } elseif (strtolower($likeLR) == 'r') {
                        $where[] = ($key . $not . ' LIKE "' . $this->escape($val) . '%"');
                    } else {
                        $where[] = ($key . $not . ' LIKE "%' . $this->escape($val) . '%"');
                    }
                    break;
                default:
                    $where[] = $key . $operator . $val;
                    break;
            }
        } elseif (isset($val)) {
            $where[] = (preg_match('/[>=<]/', $key)) ? ($key . " \"{$this->escape($val)}\"") : $key . ' = "' . $this->escape($val) . '"';
        } else {
            $where[] = $key;
        }

        if (strtolower($or) == 'or') {
            $this->orWhere = array_merge($this->orWhere, $where);
        } else {
            $this->where = array_merge($this->where, $where);
        }
        return $this;
    }

    /**
     * 返回where值
     * @return array
     */
    public function getWhere() {
        return $this->where;
    }

    /**
     * 返回orwhere值
     * @return array
     */
    public function getOr() {
        return $this->orWhere;
    }

    /**
     * @param string $table
     * @return $this
     */
    public function table($table) {
        $this->table = $table;
        return $this;
    }

    /**
     * table 别名
     * @param string $table
     * @return $this
     */
    public function from($table) {
        $this->table($table);
        return $this;
    }

    /**
     * @param string $select
     * @return $this
     */
    public function select($select) {
        $this->select = is_array($select) ? implode(',', $select) : $select;
        return $this;
    }

    /**
     * @param string||array $col
     * @return $this
     */
    public function group($col) {
        is_array($col) ? $this->group[] = $col : $this->group = array_merge($this->group, $col);
        return $this;
    }

    /**
     * @param int $num 数量
     * @param int $offset 起始位置
     * @return $this
     */
    public function limit($num, $offset = 0) {
        $this->limit = ' LIMIT ' . $offset . ',' . $num;
        return $this;
    }

    /**
     * @param string $by
     * @param string $sort
     * @return $this
     */
    public function order($by, $sort = 'ASC') {
        $this->order = ' ORDER BY ' . (is_array($by) ? implode(',', $by) : $by) . ' ' . $sort;
        return $this;
    }

    /**
     * @param string $table
     * @param string $on
     * @param string $type
     * @return $this
     */
    public function join($table, $on, $type = 'left') {
        $this->join[] = $type . ' JOIN ' . $table . ' ON ' . $on;
        return $this;
    }

    /**
     * @return string
     */
    private function buildWhere() {
        $where = $this->where ? implode(' AND ', $this->where) : null;
        $orWhere = $this->orWhere ? implode(' OR ', $this->orWhere) : null;
        return ($this->where || $this->orWhere)?('WHERE ' . $where . ($where && $orWhere ? ' OR ' . $orWhere : $orWhere)):'';
    }

    /**
     * @return string
     */
    private function buildJoin() {
        return $this->join ? implode(' ', $this->join) : '';
    }

    /**
     * 构建SQL语句
     * @return $this
     */
    public function build() {
        $where = $this->buildWhere();
        $join = $this->buildJoin();
        $group = $this->group ? " GROUP BY " . implode(',', $this->group) : '';
        $this->lastQuery = "SELECT {$this->select} FROM {$this->table} {$join} {$where}{$group}{$this->order}{$this->limit}";
        return $this;
    }

    /**
     * @param string|null $query
     * @return $this
     */
    public function query($query = null) {
        $query = $query ?: $this->lastQuery;
        if (!$this->result = $this->mysqli->query($query)) {
            $this->error = $this->mysqli->error;
        }
        return $this;
    }

    /**
     * @param string|null $table
     * @param string|null $where
     * @return $this
     */
    public function get($table = null, $where = null) {
        if ($table) $this->table($table);
        if ($where) $this->where($where);
        $this->build()
             ->query($this->lastQuery);

        return $this;
    }

    /**
     * 重置where、orWhere、result
     */
    public function reset() {
        $this->where = [];
        $this->orWhere = [];
        $this->order='';
        $this->limit='';
        $this->resultArray = [];
    }

    /**
     * 将结果值转换为array
     * @return $this
     */
    private function resultToArray() {
        $this->reset();
        if ($this->result) {
            while ($row = $this->result->fetch_object()) {
                $this->resultArray[] = $row;
            }
        }
        return $this;
    }

    /**
     * 获取结果
     * @param bool $returnArray true返回数组，false返回对象
     * @return array|object
     */
    public function result($returnArray = true) {
        $this->resultToArray();
        return ($returnArray) ? json_decode(json_encode($this->resultArray),true) : $this->resultArray;
    }

    /**
     * 获取结果的数量
     * @return int
     */
    public function count() {
        return $this->result->num_rows;
    }

    /**
     * 数据表的所有行数
     * @param string $table
     * @return int
     */
    public function countAll($table = null) {
        $table = $table ?: $this->table;
        $queryStr = 'SELECT COUNT(*) FROM ' . $table;
        $rows = $this->query($queryStr)
                     ->first();
        return $rows["COUNT(*)"];
    }

    /**
     * 获取结果的第一个值
     * @param bool $returnArray true返回数组，false返回对象
     * @return mixed|null
     */
    public function first($returnArray = true) {
        $this->resultToArray();
        return $this->resultArray ? ($returnArray ? json_decode(json_encode($this->resultArray[0]), true) : $this->resultArray[0]) : NULL;
    }

    /**
     * 获取最后查询的SQL语句
     * @return string
     */
    public function lastQuery() {
        return $this->lastQuery;
    }

    /**
     * 将结果集返回json格式
     * @return string
     */
    public function json() {
        $this->resultToArray();
        return json_encode($this->resultArray);
    }

    /**
     * @param string|array $key
     * @param string $val
     * @return $this
     */
    public function set($key, $val = null) {
        if (is_array($key)) {
            foreach ($key as $k => $v) {
                $this->field[] = $k . ' = "' . $this->escape($v) . '"';
            }
        } else {
            $this->field[] = $key . ' = "' . $this->escape($val) . '"';
        }
        return $this;
    }

    /**
     * @param string $table
     * @param string|array $data
     * @param string|array $where
     */
    public function update($table = null, $data = null, $where = null) {
        if ($table) $this->table($table);
        if ($data) $this->set($data);
        if ($where) $this->where($where);
        $where = $this->where ? ' WHERE ' . implode(' AND ', $this->where) : '';

        $this->lastQuery = 'UPDATE ' . $this->table . ' SET ' . implode(',', $this->field) . $where;
        $this->query($this->lastQuery)
             ->reset();
    }

    /**
     * @param $table
     * @param array $data
     * @param array $fields
     * @return int
     */
    public function insert($table, $data = [], $fields = []) {
        $set = [];
        $values = [];

        foreach ($data as $key => $val) {
            if (is_array($val)) {
                $cols = $fields;
                $set[] = '("' . implode('","', $this->escape($val)) . '")';
            } else {
                $fields ?: $cols[] = $key;
                $values[] = $val;
            }
        }
        $col = '(' . implode(',', $cols) . ')';
        $value = $set ? implode(',', $set) : '("' . implode('","', $this->escape($values)) . '")';

        $this->lastQuery = "INSERT INTO {$table} {$col} VALUE {$value}";
        $this->query();
        $this->insertId = $this->mysqli->insert_id;
        return $this->insertId;
    }

    /**
     * @param string $table
     * @param string|array $where
     */
    public function delete($table = '', $where = '') {
        if ($where) $this->where($where);
        if ($table) $this->table($table);
        $where = $this->buildWhere();
        $this->lastQuery = "DELETE FROM {$this->table} {$where}";
        $this->query($this->lastQuery)
             ->reset();
    }

    /**
     * 将字符转换成安全字符
     * @param $string
     * @return array
     */
    private function escape($string) {
        if (is_array($string)) {
            foreach ($string as $s) {
                $str[] = $this->mysqli->real_escape_string($s);
            }
        } else {
            $str = $this->mysqli->real_escape_string($string);
        }
        return $str;
    }

}

